﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 链接表操作类
    /// </summary>
    public class LinkDAO : ILink
    {
        /// <summary>
        /// 向链接表中插入一行数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="link">要插入的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(Link link)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Links]
                                    (
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    )
                                   VALUES 
                                    (
                                        @Name,
                                        @Url,
                                        @Title,
                                        @Target,
                                        @Type,
                                        @Visible,
                                        @Order,
                                        @ParentId
                                    )";
            return ExecuteSql(link, sql);
        }

        /// <summary>
        /// 根据链接ID删除链接表对应的一条记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除记录的链接ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Links] WHERE [link_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据链接ID数组删除链接表对应的多条记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的链接ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            //拼接要删除的ID
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Links] WHERE [link_id] IN (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新一个完整链接对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="link">要更新的对象</param>
        /// <returns>执行结果受影响行数</returns> 
        public int UpdateLink(Link link)
        {
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Links]
                                   SET
                                        [link_name]=@Name,
                                        [link_url]=@Url,
                                        [link_title]=@Title,
                                        [link_target]=@Target,
                                        [link_type]=@Type,
                                        [link_visible]=@Visible,
                                        [link_order]=@Order,
                                        [link_parent_id]=@ParentId
                                   WHERE
                                        [link_id]=@Id;";
            return ExecuteSql(link, sql);
        }

        /// <summary>
        /// 根据链接ID数组批量更新多个链接的父ID, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">链接ID数组</param>
        /// <param name="parentId">父ID(忽略则传入-1)</param>
        /// <param name="visible">更新的是否可见</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateParentId(int[] ids, int parentId, bool visible)
        {
            string ps = string.Join(",", ids);
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Links]
                                   SET
                                        [link_parent_id]=@ParentId,
                                        [link_visible]=@Visible
                                   WHERE
                                        [link_id] IN (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@ParentId", parentId));
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("link_id", "&#WednTablePrefix%&_Links");
        }

        /// <summary>
        /// 选取链接总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null)</param>
        /// <returns>总数</returns>
        public int SelectLinksCount(string where)
        {
            string sql = @"SELECT COUNT (1) FROM [&#WednTablePrefix%&_Links] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 选取链接总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null)</param>
        /// <returns>总数</returns>
        private int SelectLinksCount(string where, SqlParameter[] parmeters)
        {
            string sql = @"SELECT COUNT (1) FROM [&#WednTablePrefix%&_Links] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql, parmeters);
        }

        /// <summary>
        /// 取出当前链接表中的所有链接信息, 返回所有链接信息集合, 如果不存在则返回null
        /// </summary>
        /// <returns>所有链接信息对象集合</returns>
        public List<Link> SelectLinkList()
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Links];";
            return Select(sql);
        }

        /// <summary>
        /// 根据父ID,链接类型和是否启用选取链接对象集合, 返回链接信息对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="parentId">父ID</param>
        /// <param name="type">链接类型(0:blogroll; 1:navigation; 2:footer)</param>
        /// <param name="visible">是否是可用链接</param>
        /// <returns>链接信息对象集合</returns>
        public List<Link> SelectLinkList(int parentId, int type, bool visible)
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Links]
                                    WHERE
                                        [link_parent_id]=@ParentId
                                    AND
                                        [link_type]=@Type
                                    AND
                                        [link_visible]=@Visible
                                    ORDER BY
                                        [link_order];";
            return Select(sql,
                new SqlParameter("@ParentId", parentId),
                new SqlParameter("@Type", type),
                new SqlParameter("@Visible", visible));
        }

        /// <summary>
        /// 根据链接类型和是否启用选取链接对象集合, 返回链接信息对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="type">链接类型(0:blogroll; 1:navigation; 2:footer)</param>
        /// <param name="visible">是否是可用链接</param>
        /// <returns>链接信息对象集合</returns>
        public List<Link> SelectLinkList(int type, bool visible)
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [&#WednTablePrefix%&_Links]
                                    WHERE
                                        [link_type]=@Type
                                    AND
                                        [link_visible]=@Visible
                                    ORDER BY
                                        [link_order];";
            return Select(sql,
                new SqlParameter("@Type", type),
                new SqlParameter("@Visible", visible));
        }

        /// <summary>
        /// 根据检索条件选取链接对象集合, 返回满足条件的链接对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="keyword">关键词(忽略则传入null)</param>
        /// <param name="parentId">父ID(忽略该条件传入-1)</param>
        /// <param name="type">链接类型(0:blogroll; 1:navigation; 2:footer)</param>
        /// <param name="visible">是否是可用链接</param>
        /// <param name="pageSize">文章数目(忽略该条件传入-1)</param>
        /// <param name="pageIndex">页码(取所有者传入-1)</param>
        /// <param name="orderField">排序字段(忽略则传入null)</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)</param>
        /// <param name="recordCount">返回查询到的记录数量</param>
        /// <returns>满足条件的链接对象集合</returns>
        public List<Link> SelectLinkList(string keyword, int parentId, int type, bool visible, int pageSize, int pageIndex, string orderField, int order, out int recordCount)
        {
            List<string> conditions = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add(string.Format(" [link_name] LIKE '%{0}%' ", keyword));
            }
            conditions.Add(" [link_visible]=@Visible ");
            ps.Add(new SqlParameter("@Visible", visible));
            string where = conditions.Count > 0 ? string.Join(" AND ", conditions.ToArray()) : null;
            recordCount = SelectLinksCount(where, ps.ToArray());
            string col = @"[link_id],
                                    [link_name],
                                    [link_url],
                                    [link_title],
                                    [link_target],
                                    [link_type],
                                    [link_visible],
                                    [link_order],
                                    [link_parent_id]";
            string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Links", col, pageSize, pageIndex, orderField, order, where);
            return Select(sql, ps.ToArray());
        }

        /// <summary>
        /// 根据ID取出当前链接表中的链接对象, 如果不存在则返回null
        /// </summary>
        /// <param name="id">链接ID</param>
        /// <returns>链接对象</returns>
        public Link SelectLink(int id)
        {
            string sql = @"SELECT TOP 1
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_id]=@Id;";
            List<Link> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        #region 私有方法

        #region select
        /// <summary>
        /// 私有选取数据方法, 如果不存在则返回null
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Link> Select(string sql, params SqlParameter[] parameter)
        {
            List<Link> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Link>();
                    while (reader.Read())
                    {
                        Link link = new Link()
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Url = reader.GetString(2),
                            Title = reader.IsDBNull(3) ? null : reader.GetString(3),
                            Target = reader.GetString(4),
                            Type = reader.GetInt32(5),
                            Visible = reader.GetBoolean(6),
                            Order = reader.GetInt32(7),
                            ParentId = reader.GetInt32(8)
                        };
                        list.Add(link);
                        #region 不用
                        //Convert.ToInt32(reader["link_id"]),
                        //Convert.ToString(reader["link_name"]),
                        //Convert.ToString(reader["link_url"]),
                        //reader["link_title"] != null ? Convert.ToString(reader["link_title"]) : string.Empty,
                        //Convert.ToString(reader["link_target"]),
                        //Convert.ToString(reader["link_type"]),
                        //Convert.ToBoolean(reader["link_visible"]),
                        //Convert.ToInt32(reader["link_order"]),
                        //Convert.ToInt32(reader["link_parent_id"])); 
                        #endregion
                    }
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有执行方法执行写入数据库操作
        /// </summary>
        /// <param name="link">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteSql(Link link, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Name", link.Name),
                new SqlParameter("@Url", link.Url),
                new SqlParameter("@Title", string.IsNullOrEmpty(link.Title) ? System.DBNull.Value : (object)link.Title),
                new SqlParameter("@Target", link.Target),
                new SqlParameter("@Type", link.Type),
                new SqlParameter("@Visible", link.Visible),
                new SqlParameter("@Order", link.Order),
                new SqlParameter("@ParentId", link.ParentId),
                new SqlParameter("@Id", link.Id));
        }
        #endregion

        #endregion
    }
}
